package al.jdbc.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import al.jdbc.dpbridge.DataSource;
import al.jdbc.dpbridge.MySqlDataSource;
import alOmrane.jdbc.beans.Courrier_arrive;
import alOmrane.jdbc.beans.Moy_env;

public class Courrier_arriveDAO {
	private Connection db;
	@SuppressWarnings("unused")
	private DataSource ds;
	private ContactDAO cdao = new ContactDAO(new MySqlDataSource("jdbc:mysql://localhost/gestion_courriers", "root"));
	private Moy_envDAO mdao = new Moy_envDAO(new MySqlDataSource("jdbc:mysql://localhost/gestion_courriers", "root"));
	private Statement sql;
	public Courrier_arriveDAO(DataSource ds){
		db = ds.getConnection();
		this.ds = ds;
		try {
			sql = db.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<Courrier_arrive> findAll(){
		List<Courrier_arrive> cra = new ArrayList<Courrier_arrive>();
		try {
			String query = "SELECT * FROM courrier_arrive";
			ResultSet rs = sql.executeQuery(query);
			while (rs.next()) {
				Courrier_arrive ca = new Courrier_arrive(Integer.parseInt(rs.getString(1)),
						rs.getDate(2),
						rs.getDate(3), 
						rs.getString(4), 
						rs.getString(5),
						rs.getString(6),
						rs.getString(7),
						rs.getString(8),
						Integer.parseInt(rs.getString(9)),
						cdao.findContactById(Integer.parseInt(rs.getString(10))),
						mdao.findById(Integer.parseInt(rs.getString(11))));
				cra.add(ca);
			}
			return cra;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}
	}
	public Courrier_arrive findById(int id){
		try {
			String query = "SELECT * FROM courrier_arrive" +
					" WHERE id_courrier_arrive="+id;
			ResultSet rs = sql.executeQuery(query);
			rs.next();
			Courrier_arrive ca = new Courrier_arrive(Integer.parseInt(rs.getString(1)),
						rs.getDate(2),
						rs.getDate(3), 
						rs.getString(4), 
						rs.getString(5),
						rs.getString(6),
						rs.getString(7),
						rs.getString(8),
						Integer.parseInt(rs.getString(9)),
						cdao.findContactById(Integer.parseInt(rs.getString(10))),
						mdao.findById(Integer.parseInt(rs.getString(11))));
			return ca;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean insert(Courrier_arrive c){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		try{
			StringBuffer sb = new StringBuffer("INSERT INTO courrier_arrive " +
					"(`id_courrier_arrive`, `date_arr`, `date_expiration`, " +
					"`objet_courrier_arrive`, `ref_courrier_arrive`, " +
					"`observe_courrier_arrive`, `etat_courrier_arrive`, " +
					"`c_corr_courrier_arrive`, `delai_courrier_arrive`, " +
					"`code_ctt`, `id_moy_env`) " +
					"VALUES ('" +
					c.getId()+"','"+
					((c.getDate_arr()==null)?"0000-00-00":df.format(c.getDate_arr()))+"','"+
					((c.getDate_expiration()==null)?"0000-00-00":df.format(c.getDate_expiration()))+"','"+
					c.getObjt_courrier_arrive()+"','"+
					c.getRef_courrier_arrive()+"','"+
					c.getObserve_courrier_arrive()+"','"+
					c.getEtat_courrier_arrive()+"','"+
					c.getC_corr_courrier_arrive()+"','"+
					c.getDelai_courrier_arrive()+"','"+
					c.getCode_ctt().getCode_ctt()+"','"+
					c.getId_moy_env().getId()+"');");
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	public boolean update(Courrier_arrive c){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		try{
			StringBuffer sb = new StringBuffer("UPDATE courrier_arrive SET " +
					"date_arr='"+((c.getDate_arr()==null)?"0000-00-00":df.format(c.getDate_arr()))+"',"+
					"date_expiration='"+((c.getDate_expiration()==null)?"0000-00-00":df.format(c.getDate_expiration()))+"',"+
					"objet_courrier_arrive='"+c.getObjt_courrier_arrive()+"',"+
					"ref_courrier_arrive='"+c.getRef_courrier_arrive()+"',"+
					"observe_courrier_arrive='"+c.getObserve_courrier_arrive()+"',"+
					"etat_courrier_arrive='"+c.getEtat_courrier_arrive()+"',"+
					"c_corr_courrier_arrive='"+c.getC_corr_courrier_arrive()+"',"+
					"delai_courrier_arrive='"+c.getDelai_courrier_arrive()+"',"+
					"code_ctt='"+c.getCode_ctt().getCode_ctt()+"',"+
					"id_moy_env='"+c.getId_moy_env().getId()+"' " +
							"WHERE id_courrier_arrive="+c.getId());
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	public int incrementEngine(){
		try {
			String query = "SELECT Max(id_courrier_arrive) FROM courrier_arrive";

			ResultSet rs = sql.executeQuery(query);
			rs.next();
			return Integer.parseInt((rs.getString(1)==null)?"0":rs.getString(1))+1;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}
	
	public boolean delete(int idCourrier){
		try{
			StringBuffer sb = new StringBuffer("DELETE FROM courrier_arrive "+
					"WHERE id_courrier_arrive="+idCourrier+" ;");
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			System.out.println(ex.getMessage());
			return false;
		}
	}
	
	public boolean delete(Courrier_arrive c){
		return delete(c.getId());
	}
	
	
	
}
